USE [NLKHCNData_Prod]
GO

CREATE VIEW [dbo].[ChuyenGiaCongTrinhView]
AS
select CG.Id, 
		CG.HoTen, 
		CG.Ten, 
		CG.TenDonVi, 
		CG.DiaChiDonVi,
		CG.TenLuanAn, 
		CG.LinhVucId,
		LV.Ten as TenLinhVuc,
		NLV.Ten as TenNhomLinhVuc,
		STUFF(
			(
				select '|' + CT.TenCongTrinh
				from CongTrinhNghienCuu CT 
				where CT.ChuyenGiaId = CG.Id
				for xml path('')
			),1,1,''
		) as CongTrinh
from ChuyenGia CG
		left outer join LinhVuc LV on LV.Id = CG.LinhVucId
		left outer join NhomLinhVuc NLV on NLV.Id = LV.NhomLinhVucId

GO
